package com.cdcas;

import com.cdcas.bean.UserBean;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class MyMain {
    public static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    public static final String URL = "jdbc:mysql://localhost:3306/db_java";
    public static final String USERNAME = "root";
    public static final String PASSWORD = "root";


    public static void main(String[] args) {
        long currentPage = 1; 		// 当前所在页
        int lineSize = 2;
        try {
            Class.forName(DRIVER);
            Connection connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
            System.out.println("连接对象：" +connection);
            Statement st=connection.createStatement();
            //数据的查询
//            ResultSet ts = st.executeQuery("select * from user");
//            List<UserBean> userBeans = new ArrayList<>();
//            while (ts.next()) {
//                int uid = ts.getInt("uid");
//                String name = ts.getString("name");
//                int age = ts.getInt("age");
//                Date birthday = ts.getDate("birthdate");
//                float salary = ts.getFloat("salary");
//                String note = ts.getString("note");
//
//                UserBean userBean = new UserBean();
//                userBean.setUid(uid);
//                userBean.setName(name);
//                userBean.setAge(age);
//                userBean.setBirthday(birthday);
//                userBean.setSalary(salary);
//                userBean.setNote(note);
////                System.out.println("uid=" + uid + " name = " + name + " age=" +age + " birthday=" + birthday + " salary = " +salary
////                + " note = " + note);
//                System.out.println(userBean);
//                userBeans.add(userBean);
//            }
//            System.out.println(userBeans);
            //数据更新
            //数据添加
//            String inserSQL = "insert into user (name,age,birthdate,salary,note) values ('zhangsan1',21,'2000-01-02',5000,'yahoo.com'),('zhangsan2',20,'2000-01-02',5000,'yahoo.com')";
//            int result =st.executeUpdate(inserSQL);
//            System.out.println(result);
            //修改数据
//            String updateSql = "update user set birthdate='1999-01-01',salary=5500 where uid=6";
//            int upresult = st.executeUpdate(updateSql);
//            System.out.println("修改===》" + upresult);

            //预处理方式 PrepareStatement
//            String preSql = "insert into user (name,age,birthdate,salary,note) values (?,?,?,?,?)";
//            PreparedStatement ps = connection.prepareStatement(preSql);
//            ps.setString(1,"Mr'Lee");
//            ps.setInt(2,21);
//            ps.setDate(3,new Date(new java.util.Date().getTime()));
//            ps.setFloat(4,6802);
//            ps.setString(5,"cdcas");
//           int psResult =  ps.executeUpdate();
//            System.out.println("psResult = " + psResult);

            //分页且模糊查询
            String sql = "select *  from user where name like ? limit ?,?";
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setObject(1,"%a%");
            ps.setObject(2,(currentPage-1)*lineSize);
            ps.setObject(3,lineSize);
           ResultSet rs = ps.executeQuery();
           while (rs.next()) {
               int uid = rs.getInt("uid");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                Date birthday = rs.getDate("birthdate");
                float salary = rs.getFloat("salary");
                String note = rs.getString("note");

                UserBean userBean = new UserBean();
                userBean.setUid(uid);
                userBean.setName(name);
                userBean.setAge(age);
                userBean.setBirthday(birthday);
                userBean.setSalary(salary);
                userBean.setNote(note);
//                System.out.println("uid=" + uid + " name = " + name + " age=" +age + " birthday=" + birthday + " salary = " +salary
//                + " note = " + note);
                System.out.println(userBean);
           }

           ResultSetMetaData rsm = rs.getMetaData();
           for (int x=1;x<rsm.getColumnCount();x++) {
               System.out.println("列表名称：" + rsm.getColumnName(x)+"列表类型：" +rsm.getColumnTypeName(x) + "列类型：" + rsm.getColumnType(x));
           }






        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}
